package com.kcs.fxmargin.dao;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.kcs.common.utils.ConnectionUtils;
import com.kcs.common.utils.Constant;
import com.kcs.common.utils.OIDGenerator;
import com.kcs.common.utils.commonUtils;
import com.kcs.fxmargin.bo.CheckLimitOrderBO;
import com.kcs.fxmargin.bo.SetupRoleProfileBO;
import com.kcs.fxmargin.model.MCustInfo;
import com.kcs.fxmargin.model.TAccPos;
import com.kcs.fxmargin.model.TAccTran;
import com.kcs.fxmargin.model.TDeals;

public class CheckLimitOrderDAO {
	public CheckLimitOrderBO getTDealsDataFromRETAD(CheckLimitOrderBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		List<TDeals> resultList = null;
		TDeals td = null;
		
		try{
			sql = "SELECT NVL(US.telephone,' ') cif_number, Result.*"
				 +" FROM "
				 +" (SELECT D.gid_id deal_id "
				 +" ,NVL(D.parent#parent,' ') parent_id "
				 +" ,Case when D.fx_cross_deal#type# = 'CANCELLATION' then R.maker_account "
				 +" else D.taker_name End cust_tax_id "
                 +" ,L.type# trans_type "
                 +" ,Case when L.taker_buys_base# = 'Y' then 'B' "
                 +" else 'S' End buy_sell "
                 +" ,D.deal_date trans_date "
                 +" ,L.value_date#value_date value_date "
                 +" ,Case when D.fx_cross_deal#ccy1 in ('THS','THP','THZ') then 'THB' "
                 +" else D.fx_cross_deal#ccy1 End ccy1 "
                 +" ,Case when L.dealt_ccy <> D.fx_cross_deal#ccy1 then L.contra_amount "
                 +" else L.dealt_amount End deal_amt1 "
              	 +" ,L.quote#all_in exch_rate "
              	 +" ,Case when D.fx_cross_deal#ccy2 in ('THS','THP','THZ') then 'THB' "
              	 +" else D.fx_cross_deal#ccy2 End ccy2 "
              	 +" ,Case when L.DEALT_CCY <> D.fx_cross_deal#ccy1 then L.dealt_amount "
              	 +" else L.contra_amount End equi_amt "
              	 +" ,Case when L.DEALT_CCY <> D.fx_cross_deal#ccy1 then L.contra_amount_outstanding "
              	 +" else L.amount_outstanding End outstd_amt "
              	 +" ,Case when D.fx_cross_deal#type# = 'CANCELLATION'  or R.cancelled_by_name is not null THEN 'CANCEL' "
              	 +" else 'NEW' End doc_type "
              	 +" ,D.last_modified#last_modified last_modify "
              	 +" FROM retdba.deal D, retdba.leg L, retdba.requirement R "
              	 +" WHERE D.status = 'Completed' "
              	 +" and D.id = L.owner "
              	 +" and (D.fx_cross_deal#type# = L.type# or substr(L.type#,1,4) = 'SWAP') "
              	 +" and L.id = R.owner(+) "
              	 +" ) Result, retdba.users US "
               	 +" WHERE Result.cust_tax_id = Us.username "
               	 +" and Us.telephone = ? "
               	 +" and value_date >= to_char(sysdate, 'yyyyMMdd')";
			
				System.out.println(sql);
				System.out.println("> cifNo: "+bo.getInCifNo());
			
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, bo.getInCifNo());
				
				rs = pstmt.executeQuery();
				
				if(rs != null){
					resultList = new ArrayList<TDeals>();
					
					while(rs.next()){
						td = new TDeals();
						td.setCifNo(rs.getString("cif_number"));
						td.setDealId(rs.getString("deal_id"));
						td.setParentId(rs.getString("parent_id"));
						td.setTransType(rs.getString("trans_type"));
						td.setBuySell(rs.getString("buy_sell"));
						td.setTransDate(commonUtils.convertYYYYMMDDToSQLDate(rs.getString("trans_date")));
						td.setValueDate(commonUtils.convertYYYYMMDDToSQLDate(rs.getString("value_date")));
						td.setCcy1(rs.getString("ccy1"));
						td.setDealAmt1(rs.getBigDecimal("deal_amt1"));
						td.setExchRate(rs.getBigDecimal("exch_rate"));
						td.setCcy2(rs.getString("ccy2"));
						td.setEquityAmt(rs.getBigDecimal("equi_amt"));
						td.setOutstdAmt(rs.getBigDecimal("outstd_amt"));
						td.setDocType(rs.getString("doc_type"));

						resultList.add(td);
					}
				}
				
				bo.settDealsList(resultList);
				
				System.out.println("resultList size : "+resultList.size()+" (s).");
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public boolean insertTDeals(CheckLimitOrderBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		TDeals td = null;
		boolean result = false;
		String id = null;
		String dealStatus = null;
		String activeStatus = null;
		List<TDeals> list = null;
		
		try{
			
			list = bo.gettDealsList();

			if(list != null){
				
				for(int i=0; i<list.size(); i++){
					td = list.get(i);
	
					sql = "insert into t_deals"
					     +" (id "
					     +" , cif_no "
					     +" ,deal_id "
					     +" , parent_id "
					     +" , trans_type "
					     +" , buy_sell "
					     +" , trans_date "
					     +" , value_date "
					     +" , ccy1 "
					     +" , deal_amt1 "
					     +" , exch_rate "
					     +" , ccy2"
					     +" , equity_amt "
					     +" , outstd_amt "
					     +" , doc_type "
					     +" , im_required "
					     +" , mtm_rate "
					     +" , unrealized_pl "
					     +" , closed_rate "
					     +" , realized_pl "
					     +" , deal_status "
					     +" , active_status "
					     +" , create_by "
					     +" , create_dt "
					     +" , update_by "
					     +" , update_dt) "
					     +" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, sysdatetime(), ?, sysdatetime())";
					
					System.out.println(sql);
					
					id = OIDGenerator.create();
					dealStatus = getDealStatus(td.getValueDate());
					activeStatus = "A";
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, id);
					pstmt.setString(2, td.getCifNo());
					pstmt.setString(3, td.getDealId());
					pstmt.setString(4, td.getParentId());
					pstmt.setString(5, td.getTransType());
					pstmt.setString(6, td.getBuySell());
					pstmt.setDate(7, td.getTransDate());
					pstmt.setDate(8, td.getValueDate());
					pstmt.setString(9, td.getCcy1());
					pstmt.setBigDecimal(10, td.getDealAmt1());
					pstmt.setBigDecimal(11, td.getExchRate());
					pstmt.setString(12, td.getCcy2());
					pstmt.setBigDecimal(13, td.getEquityAmt());
					pstmt.setBigDecimal(14, td.getOutstdAmt());
					pstmt.setString(15, td.getDocType());
					pstmt.setBigDecimal(16, td.getImRequired());
					pstmt.setBigDecimal(17, td.getMtmRate());
					pstmt.setBigDecimal(18, td.getUnrealizedPl());
					pstmt.setBigDecimal(19, td.getClosedRate());
					pstmt.setBigDecimal(20, td.getRealizedPl());
					pstmt.setString(21, dealStatus);
					pstmt.setString(22, activeStatus);
					pstmt.setString(23, bo.getCreateUser());
					pstmt.setString(24, bo.getCreateUser());
					pstmt.executeUpdate();
					
					result = true;
					
				}
				
			}else{
				return false;
			}

		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return result;
	}
	
	public boolean deleteTDeals(CheckLimitOrderBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		boolean result = false;
		
		try{
			
			sql = "DELETE FROM t_deals WHERE cif_no=?";
					
					System.out.println(sql);
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, bo.getInCifNo());
					pstmt.executeUpdate();
					
					result = true;

		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return result;
	}
	
	private String getDealStatus(Date date)throws Exception{
		final String ACTIVE = "Active";
		final String MATING_AND_PENDING = "Mating&Pending";
		Calendar now = null;
		Calendar c = null;
		
		if(date != null){
			now = Calendar.getInstance();
			c = Calendar.getInstance();
			c.setTime(date);
			
			//-- Compare YEAR
			if(c.get(Calendar.YEAR) > now.get(Calendar.YEAR)){
				return ACTIVE;
			}else if(c.get(Calendar.YEAR) == now.get(Calendar.YEAR)){
				
				//-- Compare MONTH
				if(c.get(Calendar.MONTH) > now.get(Calendar.MONTH)){
					return ACTIVE;
				}else if(c.get(Calendar.MONTH) == now.get(Calendar.MONTH)){
					
					//-- Compare DATE
					if(c.get(Calendar.DATE) > now.get(Calendar.DATE)){
						return ACTIVE;
					}else if(c.get(Calendar.DATE) == now.get(Calendar.DATE)){
						return MATING_AND_PENDING;
					}
					
				}
				
			}
		
		}
		
		return "";
	}
	
	public CheckLimitOrderBO callMarginLimit(CheckLimitOrderBO bo, Connection conn)throws Exception{
		CallableStatement cstmt = null;
		ResultSet rs = null;
		String sql = null;
		
		
		try{
			sql = "{call SPCalMarginLimit(?,?)}";
			
				System.out.println(sql);
				System.out.println("> cifNo: "+bo.getInCifNo());
			
				cstmt = conn.prepareCall(sql);
				cstmt.setObject(1, bo.getInCifNo(), Types.VARCHAR);
				cstmt.registerOutParameter(2, Types.NUMERIC);
				cstmt.execute();
				
				bo.setMarginLimit(cstmt.getObject(2).toString());
				System.out.println(">"+cstmt.getObject(2));
				System.out.println(">"+bo.getMarginLimit());
			
		}catch(Exception e){
			throw e;
		}finally{
			if(cstmt != null){
				try{
					cstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public CheckLimitOrderBO getPreOrderAmount(CheckLimitOrderBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		
		try{
			sql = "select SUM(amount) amount "+
				  " from t_pre_order "+
				  " where cust_info_id = ? "+
				  " and active_status='A'";
			
				System.out.println(sql);
				System.out.println("> cifNo: "+bo.getInCifNo());
			
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, bo.getInCifNo());
				
				rs = pstmt.executeQuery();
				
				if(rs != null){
					if(rs.next()){
						bo.setPreOrderAmount(rs.getString("amount"));
					}
				}
	
				System.out.println("Pre Order Amount = "+bo.getPreOrderAmount());
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public CheckLimitOrderBO getMCustInfo(CheckLimitOrderBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		MCustInfo cust = null;
		
		try{
			sql = "select * "+
				  " from m_cust_info "+
				  " where cif_no=? "+
				  " and active_status='A'";
			
				System.out.println(sql);
				System.out.println("> cifNo: "+bo.getInCifNo());
			
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, bo.getInCifNo());
				
				rs = pstmt.executeQuery();
				
				if(rs != null){
					if(rs.next()){
						cust = new MCustInfo();
						cust.setId(rs.getString("id"));
						cust.setCifNo(rs.getString("cif_no"));
						cust.setCustName(rs.getString("cust_name"));
						cust.setMarginAccNo(rs.getString("margin_acc_no"));
						cust.setMarginAccName(rs.getString("margin_acc_name"));
						cust.setDepositAccNo(rs.getString("deposit_acc_no"));
						cust.setDepositAccName(rs.getString("deposit_acc_name"));
						cust.setCustLimitAmt(rs.getBigDecimal("cust_limit_amt"));
						cust.setCustStatusCode(rs.getString("cust_status_code"));
						cust.setActiveStatus(rs.getString("active_status"));
						cust.setCreateby(rs.getString("create_by"));
						cust.setCreateDate(rs.getDate("create_dt"));
						cust.setUpdateBy(rs.getString("update_by"));
						cust.setUpdateDate(rs.getDate("update_dt"));
						cust.setRowVersion(rs.getInt("row_version"));
						bo.setmCustInfo(cust);
					}
				}
	
				System.out.println("Pre Order Amount = "+bo.getPreOrderAmount());
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public CheckLimitOrderBO getTAccPos(CheckLimitOrderBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		TAccPos tap = null;
		
		try{
			sql = "select * "+
				  " from t_acc_pos "+
				  " where active_status='A' "+
				 // " and as_of_date = cast(GETDATE() as DATE) "+
				  " and margin_acc_no=?";
			
				System.out.println(sql);
				System.out.println("> margin_acc_no: "+bo.getmCustInfo().getMarginAccNo());
			
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, bo.getmCustInfo().getMarginAccNo());
				
				rs = pstmt.executeQuery();
				
				if(rs != null){
					if(rs.next()){
						tap = new TAccPos();
						tap.setId(rs.getString("id"));
						tap.setAsOfDate(rs.getDate("as_of_date"));
						tap.setMarginAccNo(rs.getString("margin_acc_no"));
						tap.setOutstdValue(rs.getBigDecimal("outstd_value"));
						tap.setAccruedInterest(rs.getBigDecimal("accrued_interest"));
						tap.setActiveStatus(rs.getString("active_status"));
						tap.setCreateby(rs.getString("create_by"));
						tap.setCreateDate(rs.getDate("create_dt"));
						tap.setUpdateBy(rs.getString("update_by"));
						tap.setUpdateDate(rs.getDate("update_dt"));
						tap.setRowVersion(rs.getInt("row_version"));
						bo.settAccPos(tap);
					}
				}
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public boolean insertTAccPos(CheckLimitOrderBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		boolean result = false;
		String id = null;
		BigDecimal accruedInterest = null;
		String activeStatus = null;
		int rowVersion = 0;
		MCustInfo cust = null;
		
		try{
			
			cust = bo.getmCustInfo();

			if(cust != null){
	
				sql = "insert into t_acc_pos "
				     +" (id "
				     +" , as_of_date "
				     +" , margin_acc_no "
				     +" , outstd_value "
				     +" , accrued_interest "
				     +" , active_status "
				     +" , create_by "
				     +" , create_dt "
				     +" , update_by "
				     +" , update_dt"
				     +" , row_version) "
				     +" values (?,getdate(),?,?,?,?,?, getdate(), ?, getdate(), ?)";
				
				System.out.println(sql);
				
				id = OIDGenerator.create();
				accruedInterest = new BigDecimal(0);
				activeStatus = "A";
				rowVersion = 1;
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, id);
				pstmt.setString(2, cust.getMarginAccNo());
				pstmt.setBigDecimal(3, bo.getOutsBal());
				pstmt.setBigDecimal(4, accruedInterest);
				pstmt.setString(5, activeStatus);
				pstmt.setString(6, bo.getCreateUser());
				pstmt.setString(7, bo.getCreateUser());
				pstmt.setInt(8, rowVersion);
				pstmt.executeUpdate();
				
				result = true;
				
			}else{
				return false;
			}

		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return result;
	}
	
	public void updateTAccPos(CheckLimitOrderBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		String sql = null;
		TAccPos tap = null;
		
		try{
			
			tap = bo.gettAccPos();
			
			if(tap != null){
				sql = "update t_acc_pos "+
					  " set outstd_value = ? "+
					  " ,update_by = ? "+
					  "	,update_dt = sysdatetime() "+
					  " ,row_version = ? "+
					  " where id=?";
					
					System.out.println(sql);
					System.out.println("outstd_value = "+bo.getOutsBal());
				
					pstmt = conn.prepareStatement(sql);
					pstmt.setBigDecimal(1, bo.getOutsBal());
					pstmt.setString(2, bo.getCreateUser());
					pstmt.setInt(3, tap.getRowVersion()+1);
					pstmt.setString(4, tap.getId());
					pstmt.executeUpdate();
					
			}else{
				throw new Exception("TAccPos is null.");
			}
			
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}

		}
	}
	
	public CheckLimitOrderBO getTAccTrans(CheckLimitOrderBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		TAccTran tat = null;
		
		try{
			
			sql = "select * "+
				  " from t_acc_tran "+
				  " where active_status='A' "+
				  " and seq=?";
			
				System.out.println(sql);
				System.out.println("> seq: "+bo.getSeq());
				
				
			
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, bo.getSeq());
				
				rs = pstmt.executeQuery();
				
				if(rs != null){
					if(rs.next()){
						tat = new TAccTran();
						tat.setId(rs.getString("id"));
						tat.setAccPosId(rs.getString("acc_pos_id"));
						tat.setType(rs.getString("type"));
						tat.setAmount(rs.getBigDecimal("amount"));
						tat.setSeq(rs.getInt("seq"));
						tat.setTrcCode(rs.getString("trc_code"));
						tat.setTransDate(rs.getDate("trans_date"));
						tat.setTransTime(rs.getString("trans_time"));
						tat.setEffectDate(rs.getDate("effect_date"));
						tat.setActiveStatus(rs.getString("active_status"));
						tat.setCreateby(rs.getString("create_by"));
						tat.setCreateDate(rs.getDate("create_dt"));
						tat.setUpdateBy(rs.getString("update_by"));
						tat.setUpdateDate(rs.getDate("update_dt"));
						tat.setRowVersion(rs.getInt("row_version"));
						bo.settAccTran(tat);
					}
				}
	
				System.out.println("isTAccPosHasData = "+bo.isTAccPosHasData());
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public boolean insertTAccTran(CheckLimitOrderBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		boolean result = false;
		MCustInfo cust = null;
		TAccPos tap = null;
		String id = null;
		String trcCode = null;
		String activeStatus = null;
		int rowVersion = 0;
		
		
		try{
			
			cust = bo.getmCustInfo();
			tap = bo.gettAccPos();

			if(cust != null && tap != null){
	
				sql = "insert into t_acc_tran "+
					  " (id "+
					  " , acc_pos_id  "+
					  " ,type  "+
					  " ,amount "+
					  " ,seq "+
					  " ,trc_code "+
					  " ,trans_date "+
					  " ,trans_time "+
					  " ,effect_date "+
					  " ,active_status "+
					  " ,create_by "+
					  " ,create_dt "+
					  " ,update_by "+
					  " ,update_dt "+
					  " ,row_version) "+
					  " values (?,?,?,?,?,?,?,?,?,?,?, sysdatetime(), ?, sysdatetime(), ?)";
				
				System.out.println(sql);
				
				id = OIDGenerator.create();
				trcCode = "";
				activeStatus = "A";
				rowVersion = 1;
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, id);
				pstmt.setString(2, tap.getId());
				pstmt.setString(3, bo.getDrCrFlg());
				pstmt.setBigDecimal(4, bo.getTransAmt());
				pstmt.setInt(5, bo.getTransRefNo());
				pstmt.setString(6, trcCode);
				pstmt.setDate(7, bo.getTransDt());
				pstmt.setString(8, bo.getTransTime());
				pstmt.setDate(9, bo.getEffDt());
				pstmt.setString(10, activeStatus);
				pstmt.setString(11, bo.getCreateUser());
				pstmt.setString(12, bo.getCreateUser());
				pstmt.setInt(13, rowVersion);
				pstmt.execute();
				
				result = true;
				
			}else{
				return false;
			}

		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return result;
	}
	
}
